# -*-encoding:utf8-*-
import re, sys, MySQLdb.cursors, ConfigParser,logging
from datetime import datetime
import os
from qa_tester.settings import BASE_DIR
reload(sys)
sys.setdefaultencoding('utf8')
logging.basicConfig(level=logging.DEBUG,
                    format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s [ %(message)s ]',
                    datefmt='%a, %d %b %Y %H:%M:%S',
                    filename='sql.log',
                    filemode='a+')

class DB:
    __outputsql=False #是否把sql记入日志
    __start_time=None
    __end_time=None
    _spend_time=None
    dbs = {}
    '''
    dbs = {'db0':{'host': 'localhost', 'db': 'question', 'user': 'root', 'passwd': 'root', 'port': 3306},}
    '''
    _db = ''  # 本次请求的数据库名称
    _tx = {}
    error = ''
    __lastsql = ''
    __lastInsertId = None
    db = None
    conns = {}  # 用于缓存数据库连接
    cursor = None
    _sql_keys = {'select': '', 'table': '', 'join': '', 'where': '', 'args': [], 'group': '', 'having': '', 'order': '',
                 'limit': '', 'lastsql': '', 'conn': None, 'cursor': None, 'from_table_alias': '',
                 'join_table_alias': ''}

    @classmethod
    def __getKey(cls, key):
        if key in cls._sql_keys.keys():
            return cls._sql_keys[key]
        return ''

    @classmethod
    def __setKey(cls, key, val):
        if key in cls._sql_keys.keys():
            cls._sql_keys[key] = val
            return True
        return False

    @classmethod
    def Begin(cls):
        cls._tx[cls._db] = True

    @classmethod
    def End(cls, isok):
        if isok:
            cls.conns[cls._db].commit()

        else:
            cls.conns[cls._db].rollback()
        del cls._tx[cls._db]

    @classmethod
    def GetLastSql(cls):
        return cls.__lastsql.replace('   ', ' ').replace('  ', ' ')

    @classmethod
    def __init__(cls, db=''):
        cls.__start_time=datetime.now()
        for k in cls._sql_keys:
            if type(cls._sql_keys[k]) == type(''):
                cls._sql_keys[k] = ''
            elif type(cls._sql_keys[k]) == type([]):
                cls._sql_keys[k] = []
            elif type(cls._sql_keys[k]) == type(()):
                cls._sql_keys[k] = []
        cls._db = db
        if db:
            if db not in cls.dbs.iterkeys():
                config = ConfigParser.ConfigParser()
                with open(os.path.join(BASE_DIR,'pymysql','config','config.ini'), "a+") as cfgfile:
                    config.readfp(cfgfile)
                _temp = {}
                _temp['host'] = config.get(db, 'host')
                _temp['db'] = config.get(db, 'db')
                _temp['user'] = config.get(db, 'user')
                _temp['passwd'] = config.get(db, 'passwd')
                _temp['port'] = int(config.get(db, 'port'))
                cls.db = cls.dbs[db] = _temp
            else:
                cls.db = cls.dbs[db]
            # cls.begin(db)

            try:
                cls.__getConn()
                cls.__Cursor()
            except Exception,e:
                print Exception,":",e
                logging.warning(str(e))

    @classmethod
    def Table(cls, table=''):
        if table:
            cls.__setKey('table', table)
        return cls

    @classmethod
    def __getConn(cls):
        if cls.conns.get(cls._db):
            #为了保证事务的安全每次开启事务重新建立连接
            if cls._tx.get(cls._db) and cls._tx[cls._db]:
                cls._tx[cls._db]=False
                cls.__Conn()
                return cls
            #判断上次连接是否存活,如果不存活会抛出异常重新连接
            try:
                cls.conns[cls._db].ping()
            except:
                cls.__Conn()
        else:#如果此连接之前不存在则新建立连接
            cls.__Conn()
        return cls
    @classmethod
    def __Conn(cls):
        db = cls.db
        try:
            conn = MySQLdb.connect(
                host=db['host'],
                user=db['user'],
                port=db['port'],
                passwd=db['passwd'],
                db=db['db'],
                charset='utf8',
                cursorclass=MySQLdb.cursors.DictCursor
            )
            cls.conns[cls._db] = conn
        except MySQLdb.Error, e:
            logging.warning("Mysql Error %d: %s" % (e.args[0], e.args[1]))
        return cls

    @classmethod
    def __Cursor(cls):
        cls.cursor = None
        try:
            cls.cursor = cls.conns[cls._db].cursor()
        except Exception as e:
            print str(e)
        return cls

    @classmethod
    def Select(cls, select="*"):
        if select == '':
            select = '*'
        cls.__setKey('select', 'select %s' % select)
        return cls

    @classmethod
    def From(cls, table):
        table = table.strip().replace('   ', ' ').replace('  ', ' ')
        _temp_table_list = table.split(' ')
        if len(_temp_table_list) > 1:
            cls.__setKey('from_table_alias', _temp_table_list[1])
        else:
            cls.__setKey('from_table_alias', _temp_table_list[0])
        cls.__setKey('table', 'from %s' % table)
        return cls

    @classmethod
    def Join(cls, table, condition=''):

        table = table.strip().replace('   ', ' ').replace('  ', ' ')
        _temp_table_list = table.split(' ')
        if len(_temp_table_list) > 1:
            cls.__setKey('join_table_alias', _temp_table_list[1])
        else:
            cls.__setKey('join_table_alias', _temp_table_list[0])
        condition = condition.replace(' ', '')
        if condition == '':
            pass
        if condition.find('=') > -1:
            # 如果有表别名 自动判断join条件,并自动添加表别名
            _temp_condition_list = []
            _temp_condition = condition.split('=')
            if _temp_condition[0].find('.') == -1:
                _temp_condition_list.append('%s.%s' % (cls.__getKey('from_table_alias'), _temp_condition[0]))
            else:
                _temp_condition_list.append(_temp_condition[0])
            if _temp_condition[1].find('.') == -1:
                _temp_condition_list.append('%s.%s' % (cls.__getKey('join_table_alias'), _temp_condition[0]))
            else:
                _temp_condition_list.append(_temp_condition[1])
            condition = 'on %s' % '='.join(_temp_condition_list)
        else:
            condition = 'using (%s)' % condition
        cls.__setKey('joinCondition', condition)

        # 交换表别名
        cls.__setKey('from_table_alias', cls.__getKey('join_table_alias'))
        cls.__setKey('join_table_alias', '')
        if cls.__getKey('table'):
            cls.__setKey('table', '%s left join %s %s' % (cls.__getKey('table'), table, condition))
        else:
            cls.__setKey('table', 'from %s' % table)
        return cls

    @classmethod
    def Where(cls, where, args=[]):
        if type(where) == type({}):
            for k, v in where:
                if type(v) == type({}):
                    pass  # 后期扩展为可以传入字典等形式
        elif type(where) == type(''):
            cls.__setKey('where', 'where ' + where.replace('?', '%s'))
        cls.__setKey('args', args)
        return cls

    @classmethod
    def Group(cls, groupBy=''):
        if groupBy != '':
            cls.__setKey('group', 'group by %s' % groupBy)
        return cls

    @classmethod
    def Having(cls, having):
        if having:
            cls.__setKey('having', 'having %s' % having)
        return cls

    @classmethod
    def Order(cls, order):
        order = order.strip()
        if type(order) == type({}):
            _temp_order = ','
            for k, v in order:
                _temp_order = "%s %s %s" % (_temp_order, k, v)
            _temp_order = _temp_order[1:]
        elif type(order) == type(''):
            _temp_order = order
        cls.__setKey('order', "order by %s" % _temp_order)
        return cls

    @classmethod
    def Limit(cls, start=0, offset=''):
        _temp_limit = 'limit '
        if type(start) != type(1):
            logging.warning('limit 数据类型必须为int')
            return cls
        _temp_limit += str(start)
        if offset != '':
            if type(offset) != type(1):
                logging.warning('limit 第二个参数必须为Int')
                return cls
            _temp_limit += ',' + str(offset)
        cls.__setKey('limit', _temp_limit)
        return cls

    @classmethod
    def Insert(cls, dic={}):
        key = ','.join(dic.keys())
        _temp_args = "','".join(map(str, dic.values()))
        args = tuple(dic.values())
        val = (len(dic.keys()) * ",%s")[1:]
        if cls.__getKey('table') == False:
            print 'table donot should empty'
        sql = "insert into %s ( %s ) values( %s )" % (cls.__getKey('table'), key, val)
        tt = "insert into %s ( %s ) values( %s )" % (cls.__getKey('table'), key, _temp_args)
        cls.__setKey('lastsql', tt)
        cls.__setKey('args', args)
        return cls.Query(sql)
    @classmethod
    def Inserts(cls,lists=[]):
        keys_str=''
        _values=[]
        _real_values=[]
        for key,list in enumerate(lists):
            if key==0:
                keys_str=','.join(list.keys())
            _value=[]
            _temp_list=list.values()
            for k,v in enumerate(_temp_list):
                _real_values.append(v)
                if k==0:
                    _value.append('(%s')
                elif k==(len(_temp_list)-1):
                    _value.append('%s)')
                else:
                    _value.append('%s')
            _values.append(','.join(_value))
        _values_str=','.join(_values)
        sql = "insert into %s ( %s ) values %s " % (cls.__getKey('table'), keys_str, _values_str)
        cls.__setKey('args',_real_values)
        return cls.Query(sql)
    @classmethod
    def Update(cls, data={}):
        if cls.__getKey('table') == '':
            exit('table donot should empty')
        where = cls.__getKey('where')
        if where == '':
            exit('update no where condition')
        update = []
        _temp = []
        for k in data:
            update.append(k + "=%s")
            _temp.append(data[k])
        args = cls.__getKey('args')
        _temp.extend(args)
        cls.__setKey('args', list(_temp))
        sql = "update %s set %s %s" % (cls.__getKey('table'), ','.join(update), where)
        return cls.Query(sql)

    @classmethod
    def Delete(cls):
        where = cls.__getKey('where')
        if where == '':
            exit('delete no where condition')
        sql = "delete from %s %s" % (cls.__getKey('table'), where)
        return cls.Query(sql)

    @classmethod
    def GetAll(cls):
        return cls.__makeSql()

    @classmethod
    def GetRow(cls):
        cls.__setKey('limit', 'limit 1')
        data = cls.__makeSql()
        if data and (len(data) != 0):
            return data[0]
        return {}

    @classmethod
    def __makeSql(cls):
        table = cls.__getKey('table')
        """
        # re_table = re.match("\s*from\s+(?P<table>[\w_\d]+)(\s+as\s+)*\s*(?P<alias>\w+)\s*(left)*?", table)
        print table
        re_table = re.match("\s*from\s+(?P<table>[\w_\d]+)(\s+as\s+)*\s*(?P<alias>\w+)\s*(left)*?", table)
        if re_table.groupdict()['alias']:
            table = re_table.groupdict()['alias']
        else:
            table = re_table.groupdict()['table']
        """
        # 给group 加上表名
        group = cls.__getKey('group')
        if group != '':
            group = group.replace('group by ', '').split(',')
            _group = []
            for i in group:
                if i.find('.') == -1:
                    i = table + '.' + i
                _group.append(i)
            group = 'group by ' + ','.join(_group)
        # 给order加上表名
        order = cls.__getKey('order')
        if order != '':
            order = order.replace('order by ', '').split(',')
            _order = []
            for i in order:
                _order.append(i)
            order = 'order by ' + ','.join(_order)
        sql = "%s %s %s %s %s %s %s" % (
            cls.__getKey('select'), cls.__getKey('table'), cls.__getKey('where'), group, cls.__getKey('having'), order,
            cls.__getKey('limit'))
        sql = sql.replace('  ', ' ')
        return cls.Query(sql)

    @classmethod
    def Query(cls, sql='', args=[]):
        if sql == '':
            print 'sql donot should empty'
            return False
        else:
            # 美化sql
            sql = sql.replace('?', '%s').strip()
        if len(args) == 0:
            args = cls.__getKey('args')
        # 构建lastsql,和参数过滤
        _temp_args = []
        _lastsql_args = []
        # 整合过滤对参数
        if len(args):
            for a in args:
                if type(a) == type(''):
                    _lastsql_args.append("'%s'" % a)
                else:
                    _lastsql_args.append(a)
                _temp_args.append(MySQLdb.escape_string(str(a)))

        cls.__lastsql = sql % tuple(_lastsql_args)
        if cls.__outputsql:
            logging.info(cls.GetLastSql())
        result = []
        try:  # sql执行
            rows = cls.cursor.execute(sql, tuple(_temp_args))
            if sql.upper().startswith('INSERT'):
                result = rows
                # 获取最后写入的id
                cls.__lastInsertId = cls.conns[cls._db].insert_id()
            elif sql.upper().startswith('SELECT'):
                for row in DB.cursor.fetchmany(rows):
                    result.append(row)
            else:
                result = rows
            # 是否有事务
            if cls._db not in cls._tx.keys():
                cls.conns[cls._db].commit()
        except MySQLdb.Error, e:
            cls.conns[cls._db].rollback()
            print "Mysql Error %d: %s" % (e.args[0], e.args[1])
        cls.__end_time=datetime.now()
        return result

    @classmethod
    def GetLastInsertId(cls):
        return cls.__lastInsertId
    @classmethod
    def GetSqlSpendTimes(cls):
        cls._spend_time=float((cls.__end_time-cls.__start_time).microseconds/1000)/1000
        return cls._spend_time


'''
#demo
# 另一个库查询库1
news = DB('db1').Select().From('ecs_users users').Where("phone=%s", ['1512104****']).Join('ecs_order_info info',
                                                                                          'users.user_id=info.user_id').Order(
    'user_id desc').Group('phone').GetAll()
print news

# 另一个库查询库5
admin = DB('db1').Select('user_name').From('ecs_admin_user').Where('user_name=%s', ['admin']).GetAll()
print admin

# 另一个库查询库4
news = DB('db1').Select('phone').From('ecs_users').Where("phone=%s", ['15555555555']).GetAll()
print news

# 另一个库查询库1
admin = DB('db1').Select('user_name').From('ecs_admin_user').Where('user_name=%s', ['admin']).GetAll()
print admin

# 事务 插入
data = {'agency_name': 'Jython', 'agency_desc': 'hehe'}
DB('db1').Begin()
isok = DB('db1').Table('ecs_agency').Insert(data)
print '-------------------------------'
print DB('db1').GetLastInsertId()
print DB('db1').GetLastSql()
DB('db1').End()
print isok


# 无事务插入
isok = DB('db5').Table('ecs_agency').Insert(data)
print isok

# 修改
data = {'agency_name': 'Jython', 'agency_desc': 'efefefef'}
isok = DB('db4').Table('ecs_agency').Where('agency_id=%s', [28]).Update(data)
print isok

# 删除
isok = DB('db4').Table('ecs_agency').Where('agency_id=%s', [24]).Delete()
print isok
'''
'''
# 另一个库查询库1
admin = DB('db4').Select('user_name').From('ecs_admin_user').Where('user_name=%s', ['admin']).Limit(0,2).GetAll()
print DB('db4').GetLastSql()
print admin
'''
